<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1" import="support.*, java.util.*, java.sql.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Application Submitted!</title>
</head>
<body>

<h1>Your application has been submitted for review</h1>
<p>We will try to get back to you shortly, after we have reviewed your application.</p>

<%
	Student student = (Student)session.getAttribute("student");
	ArrayList<Degree> degreeList = (ArrayList<Degree>)session.getAttribute("degreelist");
	int applicationId = 0;	
	
	if (student != null && degreeList != null)
	{
		String name = student.getFirstName() + " " + student.getMiddleName() + " " + student.getLastName();
		int country = student.getCitizenship();
		String address = student.getAddress() + ", " + student.getCity();
		if (student.getState() != "")
			address = address + ", " + student.getState();
		address = address + ", " + student.getZipcode();
		int specialization = student.getSpecialization();
		Connection connection = null;
		PreparedStatement stm = null;
		ResultSet result = null;
		Class.forName("org.postgresql.Driver");
		connection = DriverManager.getConnection(
	    "jdbc:postgresql://localhost:5432/Graduate_application_database","postgres","postgres");
		
		try
		{
			connection.setAutoCommit(false);
			stm = connection.prepareStatement("INSERT INTO applicants (name, country, address, specialization) VALUES (?,?,?,?)",Statement.RETURN_GENERATED_KEYS);
			stm.setString(1, name);
			stm.setInt(2, country);
			stm.setString(3, address);
			stm.setInt(4, specialization);

			stm.executeUpdate();
			result = stm.getGeneratedKeys();
			result.next();
			int a_id = result.getInt(1);
			applicationId = a_id;
		
			for(int j = 0; j < degreeList.size(); j++)
			{ 
				Degree d = degreeList.get(j);
				String title = d.getTitle();
				int university = 0;
				int u_id = 0;
				if (d.getUniversity() != -1)
					university = d.getUniversity();
				else
				{
					stm = connection.prepareStatement("INSERT INTO universities (country_state_id, university) VALUES (?,?)",Statement.RETURN_GENERATED_KEYS);
					stm.setInt(1, d.getLocation());
					stm.setString(2, d.getNewUniversity());
					stm.executeUpdate();
					result = stm.getGeneratedKeys();
					result.next();
					u_id = result.getInt(1);
				}
				int discipline = 0;
				int d_id = 0;
				if (d.getDiscipline() != -1)
					discipline = d.getDiscipline();
				else
				{
					stm = connection.prepareStatement("INSERT INTO disciplines (discipline) VALUES (?)",Statement.RETURN_GENERATED_KEYS);
					stm.setString(1, d.getNewDiscipline());
					stm.executeUpdate();
					result = stm.getGeneratedKeys();
					result.next();
					d_id = result.getInt(1);
				}
				int location = d.getLocation();
				String date = d.getDate();
				Double gpa = d.getGpa();		
		
				stm = connection.prepareStatement("INSERT INTO degrees (applicant, discipline, university, title, date, gpa, location) VALUES (?,?,?,?,?,?,?)");
				stm.setInt(1, a_id);
				if (d.getDiscipline() != -1)
					stm.setInt(2, discipline);
				else
					stm.setInt(2, d_id);
				if (d.getUniversity() != -1)
					stm.setInt(3, university);
				else
					stm.setInt(3, u_id);
				stm.setString(4, title);
				stm.setString(5, date);
				stm.setDouble(6, gpa);
				stm.setInt(7, location);
				stm.executeUpdate();
			}
			
			connection.commit();
			connection.setAutoCommit(true);
		
			result.close();
			stm.close();
			connection.close();
		}
		catch (SQLException e)
		{
			throw new RuntimeException(e);
		}
    	finally {
        	if (result != null) {
            	try {
                	result.close();
            	} 
            	catch (SQLException e) { }
            	result = null;
        	}
        	if (stm != null) {
            	try {
                	stm.close();
            	} 
            	catch (SQLException e) { }
            	stm = null;
        	}
        	if (connection != null) {
            	try {
                	connection.close();
            	} 
            	catch (SQLException e) { }
            	connection = null;
        	}
        	
			session.setAttribute("student", null);
			session.setAttribute("degree",null);
			session.setAttribute("degreelist", null);
    	}
	}
    
%>

<p>Your application ID is: <%=applicationId%></p>

</body>
</html>
